/*
 * Copyright (c) 2014, Hsu Shih-Chia (schsu01@gmail.com)
 * All rights reserved.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *  http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.qood;

import java.io.*;
import java.util.*;
import java.util.regex.Pattern;

/**
 * QOutput is a interface which provided many methods to convert or output data.
 * <p>Methods in QOutput has 4 purposes:<ol>
 * <li>convert data into different format like Map, Collection</li>
 * <li>output data to different place like files or OutputStream</li>
 * <li>execute simple operation like minus, intersect, transpose</li>
 * <li>get additional information like schema</li>
 * </ol>
 *
 * @author SCHsu01
 * @version 1.0
 */
public interface QOutput {
  /**
   * Convert each row into columns.
   * <p>You will get 1 + {@link QModel#getColCount()} rows, the additional row is column name.
   *
   * @return the <a href="http://en.wikipedia.org/wiki/Transpose">Transposed</a> QModel
   */
  QModel doTranspose();

  /**
   * Do set operations like <a href="http://en.wikipedia.org/wiki/Set_operations_(SQL)#EXCEPT_operator">MINUS(oracle) or EXCEPT(SQLServer)</a>
   *
   * @param byModel data for minuse
   * @param byCols  specified columns for matching data, if null will use the intersection columns between 2 QModel
   * @return the <a href="http://en.wikipedia.org/wiki/Complement_(set_theory)">Complement</a> between 2 QModel
   */
  QModel doMinus(QModel byModel, String... byCols);

  /**
   * Do set operations like <a href="http://en.wikipedia.org/wiki/Set_operations_(SQL)#INTERSECT_operator">INTERSECT</a>
   *
   * @param byModel data for intersect
   * @param byCols  specified columns for matching data, if null will use the intersection columns between 2 QModel
   * @return the <a href="http://en.wikipedia.org/wiki/Intersection_(set_theory)">Intersection</a> between 2 QModel
   */
  QModel doIntersect(QModel byModel, String... byCols);

  /**
   * Output content to QFS file (binary)
   *
   * @param pathQFS  where to output
   * @param colNames what columns to output
   * @return new QFS QModel
   */
  QModel fileQFS(String pathQFS, String... colNames);

  /**
   * Output content to <a href="http://en.wikipedia.org/wiki/Comma-separated_values">CSV file</a>
   *
   * @param pathCSV     where to output
   * @param charsetName file encoding
   * @param colNames    what columns to output
   */
  void fileCSV(String pathCSV, String charsetName, String... colNames);

//  /**
//   * Output content as text to OutputStream
//   *
//   * @param out      where to output
//   * @param rows     how many rows to output
//   * @param colNames what columns to output
//   */
//  void log(OutputStream out, IntRead rows, String... colNames);
//
//  /**
//   * Output content as text to Writer
//   *
//   * @param out      where to output
//   * @param rows     how many rows to output
//   * @param colNames what columns to output
//   */
//  void log(Writer out, IntRead rows, String... colNames);

  /**
   * Convert data to single-value Collection
   *
   * @param distinct whether remove duplicated data in single-value Collection
   * @param colName  single column as value
   * @return single-value Collection
   */
  Collection<Object> values(boolean distinct, String colName);

  /**
   * Convert data to multi-value Collection
   *
   * @param distinct whether remove duplicated data in multi-value Collection
   * @param colNames multiple columns as value
   * @return multi-value Collection
   */
  Collection<Object[]> values(boolean distinct, String... colNames);

  /**
   * Convert data to Beans with customized converter implementation
   *
   * @param convert converter implementation
   * @param <T>     Bean data type
   * @return a list of Bean
   */
  <T> List<T> values(Bean<T> convert);

  /**
   * Convert data to Map with single-value keys and QModel values
   *
   * @param keyColName    single column as key
   * @param distinct      whether remove duplicated data in QModel values
   * @param valueColNames multiple columns as QModel
   * @return Map with single-value keys and QModel values
   */
  Map<Object, QModel> mapModel(String keyColName, boolean distinct, String... valueColNames);

  /**
   * Convert data to Map with multi-value keys and QModel values
   *
   * @param keyColNames   multiple columns as key
   * @param distinct      whether remove duplicated data in QModel values
   * @param valueColNames multiple columns as QModel
   * @return Map with multi-value keys and QModel values
   */
  Map<Object[], QModel> mapModel(String[] keyColNames, boolean distinct, String... valueColNames);

  /**
   * Convert data to Map with single-value keys and list-array values
   *
   * @param keyColName    single column as key
   * @param valueColNames multiple columns as value
   * @return Map with single-value keys and list-array values
   */
  Map<Object, List<Object>[]> mapLists(String keyColName, String... valueColNames);

  /**
   * Convert data to Map with multi-value keys and list-array values
   *
   * @param keyColNames   multiple columns as key
   * @param valueColNames multiple columns as value
   * @return Map with multi-value keys and list-array values
   */
  Map<Object[], List<Object>[]> mapLists(String[] keyColNames, String... valueColNames);

  /**
   * Convert data to Map with single-value keys and single-value values
   *
   * @param keyColName   single column as key
   * @param distinct     whether remove duplicated data in single-value values
   * @param valueColName single column as value
   * @return Map with single-value keys and single-value values
   */
  Map<Object, Collection<Object>> mapValue(String keyColName, boolean distinct, String valueColName);

  /**
   * Convert data to Map with multi-value keys and single-value values
   *
   * @param keyColNames  multiple columns as key
   * @param distinct     whether remove duplicated data in single-value values
   * @param valueColName single column as value
   * @return Map with multi-value keys and single-value values
   */
  Map<Object[], Collection<Object>> mapValue(String[] keyColNames, boolean distinct, String valueColName);

  /**
   * Convert data to Map with single-value keys and multi-value values
   *
   * @param keyColName    single column as key
   * @param distinct      whether remove duplicated data in multi-value values
   * @param valueColNames multiple columns as value
   * @return Map with single-value keys and multi-value values
   */
  Map<Object, Collection<Object[]>> mapValues(String keyColName, boolean distinct, String... valueColNames);

  /**
   * Convert data to Map with multi-value keys and multi-value values
   *
   * @param keyColNames   multiple columns as key
   * @param distinct      whether remove duplicated data in multi-value values
   * @param valueColNames multiple columns as value
   * @return Map with multi-value keys and multi-value values
   */
  Map<Object[], Collection<Object[]>> mapValues(String[] keyColNames, boolean distinct, String... valueColNames);

  /**
   * Convert data to SQL statement (for oracle only)
   *
   * @param results   set how many rows in a SQL
   * @param constData additional constant data
   * @param colNames  if null use all columns
   * @return a list of SQL like "SELECT ~ FROM DUAL UNION ALL SELECT ~ FROM DUAL"
   */
  List<String> sqlSelect(int results, Map<String, ?> constData, String... colNames);

  /**
   * Gather data schema to a QModel
   *
   * @param colNames if null use all columns
   * @return QModel{NAME, SIZE, IS_CONST, TYPE, DETAIL}
   */
  QModel getSchema(String... colNames);

  /**
   * Customized converter between JavaBeans and QModel.
   *
   * @param <T> data type of JavaBean
   */
  interface Bean<T> {
    /**
     * Convert a JavaBean to an Object array
     *
     * @param p JavaBean
     * @return content of defined fields
     */
    Object[] from(T p);//each field each getter (lambda function) would be better

    /**
     * Convert an Object array to a JavaBean
     *
     * @param o content of defined fields
     * @return created JavaBean
     */
    T to(Object... o);

    /**
     * Field aliases of this JavaBean
     *
     * @return columns in QModel
     */
    String[] fields();

    /**
     * Data types of defined fields
     *
     * @return types of defined fields
     */
    Class[] types();

    /**
     * QOutput.Bean Converter implementation which using Java Reflection to get fields and types.
     */
    final class ByField<T> implements Bean<T> {
      private final Class<T> klass;
      private Map<String, ConvertField> map = new LinkedHashMap<String, ConvertField>();
      private ConvertField[] fields;
      private String[] names;
      private Class[] types;

      public ByField(Class<T> klass) {
        this.klass = klass;
      }

      public ByField<T> set(String colName, String fieldName) {
        map.put(colName, ConvertField.getInstance(klass, fieldName));
        return this;
      }

      public Object[] from(T p) {
        final int n = fields.length;
        final Object[] result = new Object[n];
        for (int i = 0; i < n; i++) result[i] = fields[i].to(p);
        return result;
      }

      public T to(Object... o) {
        try {
          final T result = klass.newInstance();
          for (int i = 0, n = o.length; i < n; i++) fields[i].set(result, o[i]);
          return result;
        } catch (Exception e) {
          throw new RuntimeException(e);
        }
      }

      private ByField<T> init() {
        if (null == map) return this;
        final int n = map.size();
        fields = new ConvertField[n];
        names = new String[n];
        types = new Class[n];
        int i = 0;
        for (Map.Entry<String, ConvertField> e : map.entrySet()) {
          names[i] = e.getKey();
          types[i] = (fields[i] = e.getValue()).toType();
          i++;
        }
        map = null;
        return this;
      }

      public String[] fields() {
        return init().names;
      }

      public Class[] types() {
        return init().types;
      }
    }

    /**
     * QOutput.Bean is an template to convert data by your customized implementation.
     */
    abstract class Custom<T> implements Bean<T> {
      private final String[] names;
      private final Class[] types;

      protected Custom(String[] names, Class... types) {
        this.names = names;
        this.types = types;
      }

      public final String[] fields() {
        return names;
      }

      public final Class[] types() {
        return types;
      }
    }
  }
}

interface LineOut {
  void line(String s);

  final class PrintLine implements LineOut {
    private final PrintStream p;

    PrintLine(OutputStream p) {
      this.p = p instanceof PrintStream ? (PrintStream) p : new PrintStream(p);
    }

    public void line(String s) {
      p.println(s);
    }
  }

  final class WriteLine implements LineOut {
    private final BufferedWriter w;

    WriteLine(Writer w) {
      this.w = w instanceof BufferedWriter ? (BufferedWriter) w : new BufferedWriter(w);
    }

    public void line(String s) {
      try {
        w.newLine();
        w.write(s);
      } catch (IOException e) {
        throw new RuntimeException(e);
      }
    }
  }
}

final class Output implements QOutput {
  private static final int LOG_ROWS = 10;
  private final Map<String, Col> colsMap = new HashMap<String, Col>();
  private final Model model;

  Output(Model model) {
    this.model = model;
    final Col[] cols = model.getCols();
    final String[] names = model.getColNames();
    for (int i = 0, n = model.getColCount(); i < n; i++) colsMap.put(names[i], cols[i]);
  }

  static Object[] getValues(int row, int cols, Col[] col, Object[] result) {
    for (int i = 0; i < cols; i++) result[i] = col[i].getRow(row);
    return result;
  }

  static Model getPartial(String[] name, Col[] cols, IntRead rows, boolean distinct) {
    final Col[] col = Col.getPartial(rows, cols);
    final int rowCnt = rows.size();
    return (Model) (distinct ? new BuilderRow(rowCnt, true, name).setTypes(col)
      .addAll(col, rowCnt).toModel() : new ModelCol(rowCnt, col, name));
  }

  private static StringBuilder log(StringBuilder sb, int last, Format[] fmt, String[] buffer) {
    for (int i = 0; i < last; i++) fmt[i].logSpace(sb, buffer[i]).append('\t');
    return sb.append(buffer[last]);
  }

  private static StringBuilder log(StringBuilder sb, int last, Format[] fmt, String[][] buffer, int rows) {
    log(sb, last, fmt, buffer[0]);//first row
    for (int row = 1; row < rows; row++) log(sb.append('\n'), last, fmt, buffer[row]);
    return sb;
  }

  private Col getCol(String colName) {
    final Col result = colsMap.get(colName);
    if (null == result) throw new IllegalArgumentException(colName + " not found in " + colsMap.keySet());
    return result;
  }

  private Format getFormat(String colName, boolean splus) {
    return Format.getInstance(getCol(colName), colName, splus);
  }

  private Format[] getFormats(boolean splus, String... colNames) {
    if (StringUtil.isEmpty(colNames)) throw new IllegalArgumentException("empty colNames!");
    final int n = colNames.length;
    final Format[] result = new Format[n];
    for (int i = 0; i < n; i++) result[i] = getFormat(colNames[i], splus);
    return result;
  }

  Col[] getCols(String... colNames) {
    if (StringUtil.isEmpty(colNames)) throw new IllegalArgumentException("empty colNames!");
    final int n = colNames.length;
    final Col[] result = new Col[n];
    for (int i = 0; i < n; i++) result[i] = getCol(colNames[i]);
    return result;
  }

  QModel getPartial(boolean keepMatch, QModel byModel, String... byCols) {
    if (StringUtil.isEmpty(byCols)) byCols = StringUtil.intersect(model.getColNames(), byModel.getColNames());
    return getPartial(keepMatch, byCols, byModel.to().values(true, byCols));
  }

  private QModel getPartial(boolean keepMatch, String[] matchCols, Collection<Object[]> matchValue) {
    final Col[] col = getCols(matchCols);
    final IntList rows = IntList.create(/*unknown size*/);
    final int cols = col.length;
    final Object[] val = new Object[cols];//reuse
    for (int i = 0, n = model.getRowCount(); i < n; i++)
      if (keepMatch == matchValue.contains(getValues(i, cols, col, val))) rows.add(i);
    return getPartial(rows.toRead(), false);
  }

  QModel getPartial(IntRead rows, boolean distinct, String... colNames) {
    return StringUtil.isEmpty(colNames) ? getPartial(model.getColNames(), model.getCols(), rows, distinct) :
      getPartial(colNames, getCols(colNames), rows, distinct);
  }

  String[] checkColNames(String... colNames) {
    return null == colNames || 0 == colNames.length ? model.getColNames() : colNames;
  }

  public QModel fileQFS(String pathQFS, String... colNames) {
    final Col[] cols = getCols(colNames = checkColNames(colNames));
    return new BuilderQFS(pathQFS, colNames).setTypes(cols).addAll(cols, model.getRowCount()).toModel();
  }

  public void fileCSV(String pathCSV, String charsetName, String... colNames) {
    final Format[] fmts = getFormats(false, colNames = checkColNames(colNames));
    BufferedWriter bw = null;
    try {
      final File fileCSV = new File(pathCSV);
      QUtil.createNewFile(fileCSV);
      bw = new BufferedWriter(new OutputStreamWriter(new BufferedOutputStream(
        new FileOutputStream(fileCSV, false)), null == charsetName ? "UTF-8" : charsetName));
      final int cols = fmts.length;
      bw.write(colNames[0]);
      for (int i = 1; i < cols; i++) bw.append(',').write(colNames[i]);
      for (int r = 0, n = model.getRowCount(); r < n; r++) {
        bw.newLine();
        fmts[0].csvData(bw, r);
        for (int i = 1; i < cols; i++) fmts[i].csvData(bw.append(','), r);
      }
    } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException(e);
    } finally {
      if (null != bw) try {
        bw.close();
      } catch (IOException ignored) {
      }
    }
  }

  public void log(OutputStream out, IntRead rows, String... colNames) {
    log(new LineOut.PrintLine(out), rows, colNames);
  }

  public void log(Writer out, IntRead rows, String... colNames) {
    log(new LineOut.WriteLine(out), rows, colNames);
  }

  private void log(LineOut out, IntRead row, String[] colNames) {
    if (null == row) row = IntRead.nSize(model.getRowCount());
    final int bufSize = LOG_ROWS, colCnt = colNames.length, rowCnt = row.size(), showCnt = colCnt + 1;
    final Format[] ori = getFormats(false, colNames = checkColNames(colNames)), fmt = new Format[showCnt];
    fmt[0] = Format.getRowId();
    System.arraycopy(ori, 0, fmt, 1, colCnt);
    final String[][] buffer = new String[bufSize][showCnt], header = new String[][]{StringUtil.unionAll(new String[]{"#"}, colNames)};
    int bufId = 0;
    final StringBuilder sb = new StringBuilder();
    for (int i = 0, r = row.get(i), c = 0, n = Math.min(rowCnt, bufSize); i < n; r = row.get(++i), c = 0)
      for (final String[] buf = buffer[bufId++]; c < showCnt; c++) buf[c] = fmt[c].logData(r);//calc first 10 rows
    log(sb, colCnt, fmt, header, 1).append('\n');//header
    for (int i = bufSize, c = 0, t = 0; i < rowCnt; i++, c = 0) {
      if (bufId == bufSize) {//for more than 10 rows
        out.line(log(sb, colCnt, fmt, buffer, bufSize).toString());//content
        sb.setLength(bufId = 0);//clear
        if (4 == t++) {//every 50 rows print header again
          log(sb, colCnt, fmt, header, 1).append('\n');//header
          t = 0;
        }
      }
      final int r = row.get(i);
      for (final String[] buf = buffer[bufId++]; c < showCnt; c++) buf[c] = fmt[c].logData(r);//calc len
    }
    out.line((bufId > 0 ? log(sb, colCnt, fmt, buffer, bufId) : sb).toString());//last content
  }

  public QModel doTranspose() {
    return ModelTrans.getInstance(model);
  }

  public QModel doMinus(QModel byModel, String... byCols) {
    return QUtil.isEmpty(byModel) ? model : getPartial(false, byModel, byCols);
  }

  public QModel doIntersect(QModel byModel, String... byCols) {
    return QUtil.isEmpty(byModel) ? getPartial(IntRead.nSize(0), false) : getPartial(true, byModel, byCols);
  }

  public Collection<Object> values(boolean distinct, String colName) {
    return distinct ? rowsMap(colName).keySet() : ColList.getSingle(IntRead.nSize(model.getRowCount()), getCol(colName));
  }

  public Collection<Object[]> values(boolean distinct, String... colNames) {
    return distinct ? rowsMap(colNames).keySet() : ColList.getMulti(IntRead.nSize(model.getRowCount()), getCols(colNames));
  }

  public <T> List<T> values(Bean<T> convert) {
    return ColList.getBean(convert, IntRead.nSize(model.getRowCount()), getCols(convert.fields()));
  }

  private Map<Object, IntList> rowsMap(String keyColName) {
    return rowsMap(getCol(keyColName), IntRead.nSize(model.getRowCount()));
  }

  private Map<Object, IntList> rowsMap(Col keyCol, IntRead row) {
    final Map<Object, IntList> result = new LinkedHashMap<Object, IntList>();
    for (int i = 0, n = row.size(); i < n; i++) {//for each row
      final int index = row.get(i);
      final Object key = keyCol.getRow(index);
      IntList rows = result.get(key);
      if (null == rows) result.put(key, rows = IntList.create(/*unknown size*/));
      rows.add(index);
    }
    return result;
  }

  private Map<Object[], IntList> rowsMap(String... keyColName) {
    return rowsMap(getCols(keyColName), IntRead.nSize(model.getRowCount()));
  }

  private Map<Object[], IntList> rowsMap(Col[] keyCols, IntRead row) {
    final int cols = keyCols.length;
    final Object[] key = new Object[cols];//reuse
    final Map<Object[], IntList> result = CollFactory.ARRAY.getMap(true);
    for (int i = 0, n = row.size(); i < n; i++) {//for each row
      IntList rows = result.get(getValues(row.get(i), cols, keyCols, key));
      if (null == rows) result.put(key.clone(), rows = IntList.create(/*unknown size*/));
      rows.add(i);
    }
    return result;
  }

  public Map<Object, QModel> mapModel(String keyColName, boolean distinct, String... valueColNames) {
    final Map<Object, IntList> rows = rowsMap(keyColName);
    final Map<Object, QModel> result = new LinkedHashMap<Object, QModel>(rows.size());
    for (final Map.Entry<Object, IntList> entry : rows.entrySet())
      result.put(entry.getKey(), getPartial(entry.getValue().toRead(), distinct, valueColNames));
    return result;
  }

  public Map<Object[], QModel> mapModel(String[] keyColNames, boolean distinct, String... valueColNames) {
    final Map<Object[], IntList> rows = rowsMap(keyColNames);
    final Map<Object[], QModel> result = CollFactory.ARRAY.getMap(true, rows.size());
    for (final Map.Entry<Object[], IntList> entry : rows.entrySet())
      result.put(entry.getKey(), getPartial(entry.getValue().toRead(), distinct, valueColNames));
    return result;
  }

  public Map<Object, List<Object>[]> mapLists(String keyColName, String... valueColNames) {
    final Map<Object, IntList> rows = rowsMap(keyColName);
    final Map<Object, List<Object>[]> result = new LinkedHashMap<Object, List<Object>[]>(rows.size());
    final Col[] values = getCols(valueColNames);
    for (final Map.Entry<Object, IntList> entry : rows.entrySet())
      result.put(entry.getKey(), ColList.getSingle(entry.getValue().toRead(), values));
    return result;
  }

  public Map<Object, Collection<Object>> mapValue(String keyColName, boolean distinct, String valueColName) {
    final Map<Object, IntList> rows = rowsMap(keyColName);
    final Map<Object, Collection<Object>> result = new LinkedHashMap<Object, Collection<Object>>(rows.size());
    final Col value = getCol(valueColName);
    for (final Map.Entry<Object, IntList> entry : rows.entrySet()) {
      final IntRead row = entry.getValue().toRead();
      result.put(entry.getKey(), distinct ? rowsMap(value, row).keySet() : ColList.getSingle(row, value));
    }
    return result;
  }

  public Map<Object, Collection<Object[]>> mapValues(String keyColName, boolean distinct, String... valueColNames) {
    final Map<Object, IntList> rows = rowsMap(keyColName);
    final Map<Object, Collection<Object[]>> result = new LinkedHashMap<Object, Collection<Object[]>>(rows.size());
    final Col[] values = getCols(valueColNames);
    for (final Map.Entry<Object, IntList> entry : rows.entrySet()) {
      final IntRead row = entry.getValue().toRead();
      result.put(entry.getKey(), distinct ? rowsMap(values, row).keySet() : ColList.getMulti(row, values));
    }
    return result;
  }

  public Map<Object[], List<Object>[]> mapLists(String[] keyColNames, String... valueColNames) {
    final Map<Object[], IntList> rows = rowsMap(keyColNames);
    final Map<Object[], List<Object>[]> result = CollFactory.ARRAY.getMap(true, rows.size());
    final Col[] values = getCols(valueColNames);
    for (final Map.Entry<Object[], IntList> entry : rows.entrySet())
      result.put(entry.getKey(), ColList.getSingle(entry.getValue().toRead(), values));
    return result;
  }

  public Map<Object[], Collection<Object>> mapValue(String[] keyColNames, boolean distinct, String valueColName) {
    final Map<Object[], IntList> rows = rowsMap(keyColNames);
    final Map<Object[], Collection<Object>> result = CollFactory.ARRAY.getMap(true, rows.size());
    final Col value = getCol(valueColName);
    for (final Map.Entry<Object[], IntList> entry : rows.entrySet()) {
      final IntRead row = entry.getValue().toRead();
      result.put(entry.getKey(), distinct ? rowsMap(value, row).keySet() : ColList.getSingle(row, value));
    }
    return result;
  }

  public Map<Object[], Collection<Object[]>> mapValues(String[] keyColNames, boolean distinct, String... valueColNames) {
    final Map<Object[], IntList> rows = rowsMap(keyColNames);
    final Map<Object[], Collection<Object[]>> result = CollFactory.ARRAY.getMap(true, rows.size());
    final Col[] values = getCols(valueColNames);
    for (final Map.Entry<Object[], IntList> entry : rows.entrySet()) {
      final IntRead row = entry.getValue().toRead();
      result.put(entry.getKey(), distinct ? rowsMap(values, row).keySet() : ColList.getMulti(row, values));
    }
    return result;
  }

  public List<String> sqlSelect(final int results, Map<String, ?> constData, String... colNames) {
    final Format[] fmts = getFormats(false, colNames = checkColNames(colNames));
    final int cols = fmts.length;
    final StringBuilder sb = new StringBuilder("SELECT ");
    fmts[0].sqlCol(sb, colNames[0], 0);
    for (int i = 1; i < cols; i++) fmts[i].sqlCol(sb.append("\n,"), colNames[i], i);
    if (null != constData) for (final Map.Entry<String, ?> entry : constData.entrySet())
      Format.sqlConst(sb.append("\n,"), entry.getValue(), entry.getKey());
    sb.append(" FROM(\nSELECT ");
    final int initSQL = sb.length(), rowCnt = model.getRowCount(), rowPer = rowCnt / results;
    return new java.util.AbstractList<String>() {
      @SuppressWarnings({"unchecked"})
      public String get(int index) {
        sb.setLength(initSQL);
        final int rowStart = index * rowPer, rowEnd = index == results - 1 ? rowCnt : rowStart + rowPer;
        fmts[0].sqlInit(sb, rowStart, 0);//1st col
        for (int i = 1; i < cols; i++) fmts[i].sqlInit(sb.append(','), rowStart, i);
        sb.append(" FROM DUAL");
        for (int r = rowStart + 1; r < rowEnd; r++) {
          sb.append("\nUNION ALL SELECT ");
          fmts[0].sqlData(sb, r);//1st col
          for (int i = 1; i < cols; i++) fmts[i].sqlData(sb.append(','), r);
          sb.append(" FROM DUAL");
        }
        return sb.append(')').toString();
      }

      public int size() {
        return results;
      }
    };
  }

  public QModel getSchema(String... colNames) {
    final Col[] cols = getCols(colNames = checkColNames(colNames));
    final int n = cols.length;
    final QBuilder.Cols b = QNew.builderHeap(n, "NAME", "SIZE", "IS_CONST", "TYPE", "DETAIL")
      .setTypes(String.class, Integer.class, Boolean.class, Class.class, String.class);
    for (int i = 0; i < n; i++) {
      final Col c = cols[i];
      b.add(colNames[i], c.expectSize(), c.isConstant(), c.getType(), c.toString());
    }
    return b.toModel();
  }
}

abstract class ColList<T> extends java.util.AbstractList<T> {
  final IntRead row;

  private ColList(IntRead row) {
    this.row = row;
  }

  static ColList<Object> getSingle(IntRead row, Col col) {
    return new Single(row, col);
  }

  @SuppressWarnings({"unchecked"})
  static ColList<Object>[] getSingle(IntRead row, Col... col) {
    final int n = col.length;
    final ColList[] result = new ColList[n];
    for (int i = 0; i < n; i++) result[i] = new Single(row, col[i]);
    return result;
  }

  static <T> ColList<T> getBean(QOutput.Bean<T> convert, IntRead row, Col... col) {
    return new Bean<T>(convert, row, col);
  }

  static ColList<Object[]> getMulti(IntRead row, Col... col) {
    return new Multi(row, col);
  }

  public int size() {
    return row.size();
  }

  static final class Single extends ColList<Object> {
    private final Col col;

    private Single(IntRead row, Col col) {
      super(row);
      this.col = col;
    }

    public Object get(int index) {
      return col.getRow(row.get(index));
    }
  }

  static final class Multi extends ColList<Object[]> {
    private final Col[] col;
    private final int cols;

    private Multi(IntRead row, Col... col) {
      super(row);
      this.col = col;
      this.cols = col.length;
    }

    public Object[] get(int index) {
      return Output.getValues(row.get(index), cols, col, new Object[cols]);
    }
  }

  static final class Bean<T> extends ColList<T> {
    private final Col[] col;
    private final int cols;
    private final QOutput.Bean<T> convert;

    private Bean(QOutput.Bean<T> convert, IntRead row, Col... col) {
      super(row);
      this.convert = convert;
      this.col = col;
      this.cols = col.length;
    }

    public T get(int index) {
      return convert.to(Output.getValues(row.get(index), cols, col, new Object[cols]));
    }
  }
}

abstract class Format<T> {
  private static final Pattern TABSP = Pattern.compile("\t|\r|\n");
  private static final Pattern COMMA = Pattern.compile("\""), APOST = Pattern.compile("'");
  private static final String LOG_NULL = "-", CSV_STAR = "\"*****\"", CSV_NA = "NA";
  private static final int LOG_MAX = 100;
  private static final char[] LOG_PAD = StringUtil.nCopies(LOG_MAX, ' ');
  private final String csvNull;
  private final String[] sqlFn;
  private final Col col;
  int len;

  Format(int initLen, Col col, boolean splus, String... sqlFn) {
    this.len = initLen;
    this.col = col;
    this.csvNull = csvNull(splus);
    this.sqlFn = sqlFn;
  }

  static Format getRowId() {
    return new Num(2, null, false) {
      @Override
      final String logData(int row) {
        final String str = Integer.toString(row + 1);
        final int l = str.length();
        if (l > len) len = l;
        return str;
      }
    };
  }

  static Format getInstance(Col col, String name, boolean splus) {
    final Class ret = col.getType();
    final int initLen = name.length();
    return Number.class.isAssignableFrom(ret) ? new Num(initLen, col, splus) :
      Date.class.isAssignableFrom(ret) ? new Time(initLen, col, splus) : new Else(initLen, col, splus);
  }

  static void sqlConst(StringBuilder sb, Object value, String name) {
    if (null == value) addName(sb.append("null"), name);
    else if (value instanceof char[]) addName(sb.append((char[]) value), name);
    else getInstance(Col.getConst(value.getClass(), value), name, false).sqlConst(sb, name);
  }

  private static String escapeSQL(String text) {
    return APOST.matcher(text).replaceAll("''");
  }

  private static String escapeCSV(String text) {
    return COMMA.matcher(escapeLog(text)).replaceAll("'");
  }

  private static String escapeLog(String text) {
    return TABSP.matcher(text).replaceAll("█");
  }

  private static void addName(StringBuilder sb, String colName) {
    sb.append(" \"").append(colName).append('"');
  }

  @SuppressWarnings({"unchecked"})
  private T value(int rowId) {
    return (T) col.getRow(rowId);
  }

  final void sqlCol(StringBuilder sb, String colName, int colId) {
    if (null == sqlFn) sb.append('M').append(colId);
    else sb.append(sqlFn[0]).append('M').append(colId).append(sqlFn[1]);
    addName(sb, colName);
  }

  final void sqlConst(StringBuilder sb, String colName) {
    if (null == sqlFn) sqlData(sb, 0);
    else {
      sqlData(sb.append(sqlFn[0]), 0);
      sb.append(sqlFn[1]);
    }
    addName(sb, colName);
  }

  final void sqlInit(StringBuilder sb, int rowId, int colId) {
    sqlData(sb, rowId);
    sb.append(" M").append(colId);
  }

  final void sqlData(StringBuilder sb, int rowId) {
    final T value = value(rowId);
    final String text;
    if (null == value || null == (text = sqlText(value))) sb.append("''");
    else sb.append('\'').append(text).append('\'');
  }

  final void csvData(Writer w, int rowId) throws IOException {
    final T value = value(rowId);
    final String text;
    if (null == value || (null == (text = csvText(value)))) w.write(csvNull);
    else csvQuote(w, text);
  }

  void csvQuote(Writer w, String s) throws IOException {
    w.append('"').append(s).append('"');
  }

  String logData(int rowId) {
    final T value = value(rowId);
    return null == value ? LOG_NULL : logText(value);
  }

  StringBuilder logSpace(StringBuilder sb, String text) {
    final int pad = len - text.length();
    return pad > 0 ? sb.append(text).append(LOG_PAD, 0, pad) : sb.append(text);
  }

  abstract String sqlText(T nonNull);

  abstract String logText(T nonNull);

  abstract String csvText(T nonNull);

  String csvNull(boolean splus) {
    return splus ? CSV_STAR : StringUtil.EMPTY_STR;
  }

  private static class Num extends Format<Number> {
    private Num(int initLen, Col col, boolean splus) {
      super(initLen, col, splus, "TO_NUMBER(", ")");
    }

    final String csvNull(boolean splus) {
      return splus ? CSV_NA : StringUtil.EMPTY_STR;
    }

    final String sqlText(Number nonNull) {
      final String ret = nonNull.toString();//may be NaN or +-Infinity => as ''
      return StringUtil.isNumber(ret) ? ret : null;
    }

    final String csvText(Number nonNull) {
      return nonNull.toString();//may be NaN or +-Infinity
    }

    @Override
    final void csvQuote(Writer w, String s) throws IOException {
      w.write(s);
    }

    final String logText(Number nonNull) {//mumFormat maybe
      final String txt = nonNull.toString();
      final int l = txt.length();
      if (l > len) len = l;
      return txt;
    }

    @Override
    final StringBuilder logSpace(StringBuilder sb, String text) {
      final int pad = len - text.length();
      return (pad > 0 ? sb.append(LOG_PAD, 0, pad) : sb).append(text);
    }
  }

  private static final class Time extends Format<Date> {
    private static final int LEN = "yyyy-mm-dd hh:nn:ss".length();

    private Time(int initLen, Col col, boolean splus) {
      super(Math.max(LEN, initLen), col, splus, "TO_DATE(", ",'yyyy-mm-dd hh24:mi:ss')");
    }

    private static String text(Date nonNull) {
      return DateUtil.toString(DateUtil.getDateTime(nonNull.getTime()), true, true, true, true, false);
    }

    final String sqlText(Date nonNull) {
      return text(nonNull);
    }

    final String csvText(Date nonNull) {
      return text(nonNull);
    }

    final String logText(Date nonNull) {
      return text(nonNull);
    }
  }

  private static final class Else extends Format<Object> {
    private static final String LOG_ETC = "...";
    private static final int LOG_LEFT = LOG_MAX - LOG_ETC.length();

    private Else(int initLen, Col col, boolean splus) {
      super(initLen, col, splus, (String[]) null);
    }

    final String sqlText(Object nonNull) {
      return escapeSQL(nonNull.toString());
    }

    final String csvText(Object nonNull) {
      return escapeCSV(nonNull.toString());
    }

    final String logText(Object nonNull) {
      final String ori = nonNull.toString(), fmt = escapeLog(ori.length() > LOG_MAX ? ori.substring(0, LOG_MAX) : ori);
      final int n = fmt.length();
      if (n > LOG_MAX) {//too long
        len = LOG_MAX;
        return fmt.substring(0, LOG_LEFT).concat(LOG_ETC);//trim to size
      } else if (n > len) len = n;
      return fmt;
    }
  }
}

